﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using ExaminationSys.DataAccess.Entity;
using DapperExtensions;
using SQLinq;
using SQLinq.Dapper;


namespace ExaminationSys.DataAccess.Repository
{
    public class SubSectionInfoRepository : Repository<SubSectionInfo>
    {
        public SubSectionInfoRepository(SqlConnection context)
            : base(context)
        {
            db = context;
        }

        private SqlConnection db;

        public override SubSectionInfo GetById(dynamic id)
        {
            return base.ObjectSet.Get<SubSectionInfo>(db, id, null, null);
        }
    }

    public class SubSectionInfoService
    {
        private SubSectionInfoService()
        {
        }

        public List<SubSectionInfo> GetAll()
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.SubSectionInfo.GetList();
                return result.ToList();
            }
        }

        //public List<SubSectionInfo> GetListByKnowlegdeLevel(int knowlegdeLevel)
        //{
        //    using (var db = DataContent.DBServer())
        //    {
        //        var items = db.Query(from p in new SQLinq<SubSectionInfo>()
        //                             where p.KnowledgeLevel == knowlegdeLevel && p.Status != 0
        //                             select p).ToList();
        //        return items;
        //    }
        //}


        public List<SubSectionInfo> GetListByKnowlegdeLevel(int knowlegdeLevel)
        {
            using (var db = DataContent.DBServer())
            {
                var items = db.Query<SubSectionInfo>(
                    @"SELECT t.* FROM SubSectionInfo t WITH(NOLOCK) 
                        INNER JOIN SubSectionDetailInfo ssdi  WITH(NOLOCK) ON t.ID=ssdi.SubSectionID
                        WHERE t.[Status]<>0  AND ssdi.[Status]<>0 and t.KnowledgeLevel=" +
                    knowlegdeLevel + "  ORDER BY t.OrderIndex ASC, t.OpeatorTime DESC ")
                    .ToList();
                return items;
            }
        }

        public List<SubSectionInfo> GetListByKnowlegdeLevel(int knowlegdeLevel,string sectionId)
        {
            using (var db = DataContent.DBServer())
            {
                var items = db.Query<SubSectionInfo>(
                    @"SELECT t.* FROM SubSectionInfo t WITH(NOLOCK) 
                        INNER JOIN SubSectionDetailInfo ssdi  WITH(NOLOCK) ON t.ID=ssdi.SubSectionID
                        WHERE t.[Status]<>0  AND ssdi.[Status]<>0 and t.KnowledgeLevel=" +
                    knowlegdeLevel + " and t.SectionID='" + sectionId +
                    "' ORDER BY t.OrderIndex ASC, t.OpeatorTime DESC ")
                    .ToList();
                return items;
            }
        }
        public List<SubSectionInfo> GetListByParentId(Guid id)
        {
            using (var db = DataContent.DBServer())
            {
                var items = db.Query(from p in new SQLinq<SubSectionInfo>()
                                     where p.ParentID == id && p.ID != id && p.Status != 0
                                     select p).ToList();
                return items;
            }
        }
        public bool Lock(List<string> ids, bool _lock)
        {
            if (ids == null || ids.Count == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                foreach (var id in ids)
                {
                    retCode = db.Execute(@"update SubSectionInfo set Lock=@Lock where ID=@TmpId",
                                         new[] {new {Lock = _lock, TmpId = id}});
                    if (retCode <= 0)
                    {
                        break;

                    }
                }
                flag = true;
            }
            return flag;
        }

        public List<SubSectionInfo> GetPublishList(string sectionId)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                pg.Predicates.Add(Predicates.Field<SubSectionInfo>(f => f.Status, Operator.Eq, 1));
                pg.Predicates.Add(Predicates.Field<SubSectionInfo>(f => f.SectionID, Operator.Eq, sectionId));
                var result = work.SubSectionInfo.GetList(pg);
                return result.ToList();
            }
        }
        public List<SubSectionInfo> GetPublishList()
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var pg = new PredicateGroup {Operator = GroupOperator.And, Predicates = new List<IPredicate>()};
                pg.Predicates.Add(Predicates.Field<SubSectionInfo>(f => f.Status, Operator.Eq, 1));
                var result = work.SubSectionInfo.GetList(pg);
                return result.ToList();
            }
        }


        public Guid GetTopParentId(Guid parentId)
        {
            Guid id = parentId;
            using (var db = DataContent.DBServer())
            {
                /*
                 1 1
                 2 1
                 3 2
                 4 2
                 */
                var item = db.Query(from p in new SQLinq<SubSectionInfo>()
                                    where p.ID == parentId
                                    select p).First();

                if (item.ID != item.ParentID)
                {
                    id = GetTopParentId(item.ParentID);
                }

                return id;
            }
        }

        public bool HasSubNodes(Guid parentId)
        {
            using (var db = DataContent.DBServer())
            {
                /*
                 1 1
                 2 1
                 3 2
                 4 2
                 */
                var item = db.Query(from p in new SQLinq<SubSectionInfo>()
                                    where p.ParentID == parentId && p.ID != p.ParentID
                                    select p).FirstOrDefault();
                return item != null;
            }
        }


        public bool CheckNameExists(string name, Guid id, Guid parentId)
        {
            using (var db = DataContent.DBServer())
            {
                var result = db.Query(from p in new SQLinq<SubSectionInfo>()
                                      where p.ID != id && p.Name == name && p.ParentID == parentId
                                      select p).FirstOrDefault();
                return result != null;
                //var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                //pg.Predicates.Add(Predicates.Field<SubSectionInfo>(f => f.Name, Operator.Eq, name));
                //pg.Predicates.Add(Predicates.Field<SubSectionInfo>(f => f.ID, Operator.Eq, id, true));
                //IUnitOfWork work = new UnitOfWork(db);
                //var result = work.SubSectionInfo.GetList(pg).FirstOrDefault();
                //return result != null;
            }
        }


        public SubSectionInfo Get(Guid id)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.SubSectionInfo.GetById(id);
                return result;
            }
        }

        public int LastestIndex(Guid id)
        {
            using (var db = DataContent.DBServer())
            {
                var result =
                    db.Query<int>("select top 1 OrderIndex from SubSectionInfo  where parentid='" + id + "' order by OrderIndex desc").
                        FirstOrDefault();
                return result;
                //var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                //pg.Predicates.Add(Predicates.Field<SubSectionInfo>(f => f.Name, Operator.Eq, name));
                //pg.Predicates.Add(Predicates.Field<SubSectionInfo>(f => f.ID, Operator.Eq, id, true));
                //IUnitOfWork work = new UnitOfWork(db);
                //var result = work.SubSectionInfo.GetList(pg).FirstOrDefault();
                //return result != null;
            }
        }

        public bool Delete(string _id)
        {
            if (string.IsNullOrWhiteSpace(_id)) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                retCode = db.Execute(@"update SubSectionInfo set status=0 where ID=@TmpId",
                                     new[] {new {TmpId = _id}});
                flag = retCode > 0;
            }
            return flag;
        }

        public bool Update(SubSectionInfo entity)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                return work.SubSectionInfo.Update(entity);
            }
        }
        public int Insert(SubSectionInfo entity)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
              
                var str =
                    @"INSERT INTO SubSectionInfo
                    (
	                    ID,
	                    SectionID,
	                    Name,
	                    KnowledgeLevel,
	                    SubSectionType,
	                    ParentID,
	                    [Status],
	                    Opeator,
	                    OpeatorTime,
	                    NodeType,
	                    Lock,
	                    OrderIndex
                    )
                    VALUES
                    (
	                    @ID,
	                    @SectionID,
	                    @Name,
	                    @KnowledgeLevel,
	                    @SubSectionType,
	                    @ParentID,
	                    @Status,
	                    @Opeator,
	                    @OpeatorTime,
	                    @NodeType,
	                    @Lock,
	                    @OrderIndex
                    )";

               return db.Execute(str, new
                                    {
                                        ID = entity.ID,
                                        SectionID = entity.SectionID,
                                        Name = entity.Name,
                                        KnowledgeLevel = entity.KnowledgeLevel,
                                        SubSectionType = entity.SubSectionType,
                                        ParentID = entity.ParentID,
                                        Status = entity.Status,
                                        Opeator = entity.Opeator,
                                        OpeatorTime = entity.OpeatorTime,
                                        NodeType = entity.NodeType,
                                        Lock = entity.Lock,
                                        OrderIndex = entity.OrderIndex
                                    });

                //return work.SubSectionInfo.Insert(entity, null);
            }
        }
    }
}